package uk.co.lunagroup.vladar.oncentersanity;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

import javax.swing.JOptionPane;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;

/**
 * . class to handle various operation on excel sheet
 * 
 * @author robert.kowalski
 * 
 */
public class ExcelOperations {
	/**
	 * . returns Sheet.
	 * 
	 * @param file
	 *            file from which data should be taken
	 * @param sheetName
	 *            name of the sheet where from get data
	 * @return - worksheet as HSSF object
	 */

	public static HSSFSheet openExcelFile(final String file,
			final String sheetName) {

		HSSFSheet worksheet = null;

		try {
			FileInputStream iStream = new FileInputStream(file);
			HSSFWorkbook workbook = new HSSFWorkbook(iStream);
			worksheet = workbook.getSheet(sheetName);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (NullPointerException e) {
			e.printStackTrace();
		}

		return worksheet;
	}

	/**
	 * . returns an array containing data from excel file
	 * 
	 * @param worksheet
	 *            worksheet where from get data
	 * @param columnCount
	 *            how many columns to import
	 * @return - 2 dimensional array of string
	 */
	public static String[][] readData(final HSSFSheet worksheet,
			final int columnCount) {

		// declare 2 dimensional array and initialize this table

		int rowCount = 0;

		// get the last number of the used rows
		try {
			rowCount = worksheet.getLastRowNum() + 1;

			// if wrong worksheet name catch exception
			// TODO allow user to browse through sheets??
		} catch (NullPointerException e) {
			JOptionPane
					.showMessageDialog(
							null,
							"Wrong worksheet name. Program will shut down. Choose another file or generate "
									+ "new file from TakeOff", "Failure",
							JOptionPane.ERROR_MESSAGE);
			// leave system
			System.exit(1);
		}

		// initialize 2 dimensional array to exact column/rows
		String[][] dataArray = null;
		dataArray = new String[columnCount][rowCount];

		for (int i = 0; i < rowCount; i++) {

			// get row
			HSSFRow row = worksheet.getRow(i);

			for (int j = 0; j < columnCount; j++) {
				// get cell value. if there is blank cell create null
				HSSFCell cell = row.getCell(j,
						org.apache.poi.ss.usermodel.Row.CREATE_NULL_AS_BLANK);
				// convert cell values into string
				String cellConvertedValue = convertCellValuesInString(cell);
				dataArray[j][i] = cellConvertedValue;
			}
		}

		return dataArray;
	}

	/**
	 * . Convert values from cell into string
	 * 
	 * @param cell
	 * @return cell value as String
	 */

	public static String convertCellValuesInString(final HSSFCell cell) {

		String convertedValue = "";

		int cellType = cell.getCellType();
		if (cellType == Cell.CELL_TYPE_STRING) {
			// convertedValue=cell.getRichStringCellValue().getString();
			// not required as in previous procedure converting this to string
			convertedValue = cell.getStringCellValue();
		} else if (cellType == Cell.CELL_TYPE_NUMERIC) {
			convertedValue = (String.valueOf(cell.getNumericCellValue()));
		} else if (cellType == Cell.CELL_TYPE_BOOLEAN) {
			convertedValue = (String.valueOf(cell.getBooleanCellValue()));
		} else if (cellType == Cell.CELL_TYPE_FORMULA) {
			convertedValue = (String.valueOf(cell.getCellFormula()));
		}
		return convertedValue;
	}

	/**
	 * creates sheet with sheetName in workbook
	 * 
	 * @param workbook
	 * @param sheetName
	 */
	public static void createSheet(final HSSFWorkbook workbook,
			final String sheetName) {
		workbook.createSheet(sheetName);
	}

	/**
	 * writes data from an String[][] array to the sheet. all data are written
	 * as text
	 * 
	 * @param sheet
	 * @param array
	 * @return sheet with written data
	 */
	public static HSSFSheet writeDataAsString(final HSSFSheet sheet,
			final String[][] array) {
		// we have to create number of rows which reflect to array size
		int rowNum = ArrayOperations.checkArrayYSize(array);
		int columnNum = ArrayOperations.checkArrayXSize(array);

		for (int i = 0; i < rowNum; i++) {
			HSSFRow row = sheet.createRow((short) i);

			for (int j = 0; j < columnNum; j++) {
				// create cell

				HSSFCell cell = row.createCell(j);
				// cell.setCellValue("test");
				cell.setCellValue(array[j][i]);
			}
		}
		return sheet;
	}

	/**
	 * writes data from an String[][] array to file with file name. in workbook
	 * a sheet with sheetName is created to store data
	 * 
	 * @param file
	 * @param sheetName
	 * @param array
	 */
	public static void writeWorkbook(final String file, final String sheetName,
			final String[][] array) {
		try {
			FileOutputStream oStream = new FileOutputStream(file);
			HSSFWorkbook workbook = new HSSFWorkbook();
			HSSFSheet worksheet = workbook.createSheet(sheetName);
			// now write data from table
			// writeDataAsString(worksheet, array);
			writeDataAsTypes(worksheet, array);
			workbook.write(oStream);
			oStream.flush();
			oStream.close();
		} catch (FileNotFoundException e) {
			JOptionPane.showMessageDialog(null,
					"Can not write to a file. Check if file is not open in "
							+ "another programme and try again", "Write error",
					JOptionPane.ERROR_MESSAGE);
			e.printStackTrace();
			System.exit(1);
		} catch (IOException e) {
			e.printStackTrace();
		} catch (NullPointerException e) {
			e.printStackTrace();
		}
	}

	/**
	 * writes data from an String[][] array to the sheet
	 * 
	 * @param sheet
	 * @param array
	 * @return sheet with written data
	 */
	public static HSSFSheet writeDataAsTypes(final HSSFSheet sheet,
			final String[][] array) {

		// we have to create number of rows which reflect to array size
		int rowNum = ArrayOperations.checkArrayYSize(array);
		int columnNum = ArrayOperations.checkArrayXSize(array);
		int qty1 = 4; // column number which should be double
		int height = 2; // second column should be written as double as well

		for (int i = 0; i < rowNum; i++) {
			HSSFRow row = sheet.createRow((short) i);

			for (int j = 0; j < columnNum; j++) {

				// create cell
				HSSFCell cell = row.createCell(j);
				// if column is qty1 then write value as double
				// do not apply to first row which title
				if (((j == qty1) || (j == height)) && (i != 0)) {
					double qty1Value = StringOperations
							.convertStringToDouble(array[j][i]);
					cell.setCellValue(qty1Value);
				} else {
					cell.setCellValue(array[j][i]);
				}

			}
		}
		return sheet;
	}
}
